﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
//using System.Data.SqlServerCe;
using System.Data;
using System.Data.OleDb;
//using ErikEJ.Sql;
using JustMVCTest.Models;

namespace PAPI_Import
{
    partial class Program
    {
        static void OperateWorkMode1()
        {
                //STEP 0 - Verify excel file validity
                if (!isValidSAPExportFile("..\\..\\App_Data\\" + sSAPFilename))
                {
                    writeToLog("Bad Excel import file. Exiting.");
                    return;
                }

                //STEP 1 - Import Excel to Side Table
                //importDataFromExcel("..\\..\\App_Data\\SAP_POes.xls", TableType.POes); //INIT MAIN DB
                importDataFromExcel("..\\..\\App_Data\\" + sSAPFilename, TableType.PO_Import);
                //importDataFromExcel("POes", "C:\\webs\\papi_import\\PAPI_Import\\App_Data\\SAP_POes.xls",true); //INIT MAIN DB
                //importDataFromExcel("PO_Import","C:\\webs\\papi_import\\PAPI_Import\\App_Data\\SAP_EXPORT.xls",false);

                int resultAffected;
                         
                //STEP 2 - Handle new POs
                resultAffected = HandleNewPOs();
                writeToLog("New POs Entered into system: " + resultAffected);

                //STEP 3 - Handle closed POs
                resultAffected = HandleClosedPOs();
                writeToLog("Closed POs updated: "+ resultAffected);

                //STEP 4 - Handle POs that changed
                resultAffected = HandleUpdatedPOs();
                writeToLog("POs updated: " + resultAffected);

                return;
        }

        static bool isValidSAPExportFile(string excelFilePath)
        {
            string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";
            
            OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);

            string myExcelDataQuery = @"   SELECT TOP 1
                                    *
                                    FROM ["+ sSheetName +"$]";

            OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
            OleDbConn.Open();

            OleDbDataReader dr;
            
            try
            {
                dr = OleDbCmd.ExecuteReader();
            }
            catch (OleDbException e)
            {
                OleDbConn.Close();
                return false;
            }

            if (dr.FieldCount < 21 || (!dr.HasRows))
            {
                
                dr.Close();
                OleDbConn.Close();
                return false;
            }

            /*
            bool myBool1 = dr.HasColumn("cost elem");
            bool myBool2 = dr.HasColumn("Int# Order");
            bool myBool3 = dr.HasColumn("[vendor]");

            System.Diagnostics.Debug.WriteLine("starting fields");
            for (int i = 0; i < dr.FieldCount; i++)
            {
                System.Diagnostics.Debug.WriteLine(dr.GetName(i));
            }
            System.Diagnostics.Debug.WriteLine("ending fields");
             */

            bool allFound = dr.HasColumn("Cost#Cent") && dr.HasColumn("Int# Order") && dr.HasColumn("Prof#Cent#") && dr.HasColumn("cost elem") && dr.HasColumn("co#el#name") &&
                        dr.HasColumn("vendor") && dr.HasColumn("PO Number") && dr.HasColumn("Item") && dr.HasColumn("Descr#") &&
                        dr.HasColumn("UM") && dr.HasColumn("T#Curr") && dr.HasColumn("Vendor Name") && dr.HasColumn("Sch#Lin#") && dr.HasColumn("Requisitio") &&
                        dr.HasColumn("Ord#Qty") && dr.HasColumn("Open#Qty") && dr.HasColumn("Order Amt#") && dr.HasColumn("Open Amt#T") &&
                        dr.HasColumn("Open Amt#L") && dr.HasColumn("Delivery D") && dr.HasColumn("Purchase O");

            dr.Close();
            OleDbConn.Close();
            return allFound;
        }

        static int HandleNewPOs()
        {
            
            //Execute a query to erase any previous data from our destination table

            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            SqlConnection SqlConnForInsert = new SqlConnection(sSqlConnectionString);

            string sClearSQL = @"   SELECT Cost_Cent, Int_Order, Prof_Cent, cost_elem, co_el_name, Vendor, PONumber, Item, Descr, UM, T_Curr, VendorName,
                                    Sch_Lin, Requisitio, Ord_Qty, Open_Qty, Order_Amt, Open_Amt_T, Open_Amt_L, DeliveryDate, Purchase_O
                                    FROM PO_Import
                                    WHERE Requisitio<>'' AND NOT EXISTS (
                                    SELECT *
                                    FROM POes
                                    WHERE PONumber = PO_Import.PONumber
                                    AND Item = PO_Import.Item)";

            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);

            string sInsertSQL = @"  INSERT INTO POes(PONumber,Item,Update_Num,latest,Status,Purchase_O,
                                                    TSSC_Pending_Send_Date,Requisitioner,DeliveryDate,
                                                    DeliveryDate_Req,Cost_Cent,Int_Order,Prof_Cent,Percentage,
                                                    Percentage_Update_Date,cost_elem,co_el_name,Vendor,VendorName,
                                                    Descr,UM,T_Curr,Sch_Lin,Ord_Qty,Open_Qty,Open_Amt_T,
                                                    Order_Amt,Open_Amt_L,Insert_Date,Update_Date)
                                                VALUES 
                                                    (@PONumber,@Item,@Update_Num,@latest,@Status,@Purchase_O,
                                                    @TSSC_Pending_Send_Date,@Requisitioner,@DeliveryDate,
                                                    @DeliveryDate_Req,@Cost_Cent,@Int_Order,@Prof_Cent,@Percentage,
                                                    @Percentage_Update_Date,@cost_elem,@co_el_name,@Vendor,@VendorName,
                                                    @Descr,@UM,@T_Curr,@Sch_Lin,@Ord_Qty,@Open_Qty,@Open_Amt_T,
                                                    @Order_Amt,@Open_Amt_L,@Insert_Date,@Update_Date)";

            SqlCommand SqlInsertCmd = new SqlCommand(sInsertSQL, SqlConnForInsert);

            SqlConn.Open();
            SqlConnForInsert.Open();

            SqlDataReader dr = SqlCmd.ExecuteReader();


            int counter = 0;

            while (dr.Read())
            {
                counter++;
                SqlInsertCmd.Parameters.Clear();

                /*
                if (dr.GetString(dr.GetOrdinal("Requisitio")) == "")   //Ignorting rows with empty Requisitioner
                {
                    continue;
                }
                 */

                SqlInsertCmd.Parameters.AddWithValue("PONumber", dr.GetInt64(dr.GetOrdinal("PONumber")));
                SqlInsertCmd.Parameters.AddWithValue("Item", dr.GetInt32(dr.GetOrdinal("Item")));
                SqlInsertCmd.Parameters.AddWithValue("Update_Num", 1);
                SqlInsertCmd.Parameters.AddWithValue("latest", 1);
                SqlInsertCmd.Parameters.AddWithValue("Status", 0);
                SqlInsertCmd.Parameters.AddWithValue("Purchase_O", dr.GetSqlDateTime(dr.GetOrdinal("Purchase_O")));
                SqlInsertCmd.Parameters.AddWithValue("TSSC_Pending_Send_Date", "01/01/2000");
                SqlInsertCmd.Parameters.AddWithValue("Requisitioner", dr.GetString(dr.GetOrdinal("Requisitio")));
                SqlInsertCmd.Parameters.AddWithValue("DeliveryDate", dr.GetSqlDateTime(dr.GetOrdinal("DeliveryDate")));
                SqlInsertCmd.Parameters.AddWithValue("DeliveryDate_Req", "01/01/2000");
                SqlInsertCmd.Parameters.AddWithValue("Cost_Cent", dr.GetString(dr.GetOrdinal("Cost_Cent")));
                SqlInsertCmd.Parameters.AddWithValue("Int_Order", dr.GetString(dr.GetOrdinal("Int_Order")));
                SqlInsertCmd.Parameters.AddWithValue("Prof_Cent", dr.GetString(dr.GetOrdinal("Prof_Cent")));
                SqlInsertCmd.Parameters.AddWithValue("Percentage", 0);
                SqlInsertCmd.Parameters.AddWithValue("Percentage_Update_Date", "01/01/2000");
                SqlInsertCmd.Parameters.AddWithValue("cost_elem", dr.GetInt32(dr.GetOrdinal("cost_elem")));
                SqlInsertCmd.Parameters.AddWithValue("co_el_name", dr.GetString(dr.GetOrdinal("co_el_name")));
                SqlInsertCmd.Parameters.AddWithValue("Vendor", dr.GetInt32(dr.GetOrdinal("Vendor")));
                SqlInsertCmd.Parameters.AddWithValue("VendorName", dr.GetString(dr.GetOrdinal("VendorName")));
                SqlInsertCmd.Parameters.AddWithValue("Descr", dr.GetString(dr.GetOrdinal("Descr")));

                if (!dr.IsDBNull(dr.GetOrdinal("UM")))
                {
                    SqlInsertCmd.Parameters.AddWithValue("UM", dr.GetSqlString(dr.GetOrdinal("UM")));
                }
                else
                {
                    SqlInsertCmd.Parameters.AddWithValue("UM", "");
                }

                SqlInsertCmd.Parameters.AddWithValue("T_Curr", dr.GetString(dr.GetOrdinal("T_Curr")));
                SqlInsertCmd.Parameters.AddWithValue("Sch_Lin", dr.GetString(dr.GetOrdinal("Sch_Lin")));
                SqlInsertCmd.Parameters.AddWithValue("Ord_Qty", dr.GetFloat(dr.GetOrdinal("Ord_Qty")));
                SqlInsertCmd.Parameters.AddWithValue("Open_Qty", dr.GetFloat(dr.GetOrdinal("Open_Qty")));
                //SqlInsertCmd.Parameters.AddWithValue("Order_Amt_T", dr.GetFloat(18));
                SqlInsertCmd.Parameters.AddWithValue("Open_Amt_T", dr.GetFloat(dr.GetOrdinal("Open_Amt_T")));
                SqlInsertCmd.Parameters.AddWithValue("Order_Amt", dr.GetFloat(dr.GetOrdinal("Order_Amt")));
                SqlInsertCmd.Parameters.AddWithValue("Open_Amt_L", dr.GetFloat(dr.GetOrdinal("Open_Amt_L")));
                SqlInsertCmd.Parameters.AddWithValue("Insert_Date", System.DateTime.Now);
                SqlInsertCmd.Parameters.AddWithValue("Update_Date", System.DateTime.Now);
                
                SqlInsertCmd.ExecuteNonQuery();
            }

            dr.Close();
            SqlConn.Close();
            SqlConnForInsert.Close();

            return counter;
        }

        static int HandleClosedPOs()
        {
            SqlConnection SqlConn0 = new SqlConnection(sSqlConnectionString);
            SqlConnection SqlConn1 = new SqlConnection(sSqlConnectionString);
            SqlConnection SqlConn2 = new SqlConnection(sSqlConnectionString);

            //PONumber, Item, update_num
            
            
            string sClearSQL0 = @"  Select 
                                    t1.Cost_Cent, t1.Int_Order, t1.Prof_Cent, t1.cost_elem, t1.co_el_name, t1.Vendor, t1.PONumber, 
                                    t1.Item, t1.Descr, t1.UM, t1.T_Curr, t1.VendorName, t1.Sch_Lin, t1.Requisitio, t1.Ord_Qty, 
                                    t1.Open_Qty, t1.Order_Amt, t1.Open_Amt_T, t1.Open_Amt_L, t1.DeliveryDate, t1.Purchase_O,
                                    t2.Update_Num, t2.TSSC_Pending_Send_Date, t2.DeliveryDate_Req, t2.Percentage, t2.Percentage_Update_Date
                                    from PO_Import t1
                                    INNER Join POes t2 ON t1.PONumber = t2.PONumber AND t1.Item = t2.Item 
                                    Where t2.latest=1 AND t2.status<>99 AND t1.Open_Amt_L=0 AND t1.Requisitio<>'' ";
            /*
            string sClearSQL0 = @"   SELECT *
                                    FROM POes
                                    WHERE latest=1 AND status<>99
                                    AND EXISTS (
                                    SELECT *
                                    FROM PO_Import
                                    WHERE PONumber = POes.PONumber AND Item = POes.Item AND Open_Amt_L=0)";
             */

            SqlCommand SqlCmd0 = new SqlCommand(sClearSQL0, SqlConn0);

            string sClearSQL1 = @"  UPDATE POes 
                                    SET latest=0, Update_Date=@Update_Date
                                    WHERE PONumber=@PONumber AND Item=@Item AND latest=1";
            
            SqlCommand SqlCmd1 = new SqlCommand(sClearSQL1, SqlConn1);

            string sClearSQL2 = @"  INSERT INTO POes(PONumber,Item,Update_Num,latest,Status,Purchase_O,
                                                    TSSC_Pending_Send_Date,Requisitioner,DeliveryDate,
                                                    DeliveryDate_Req,Cost_Cent,Int_Order,Prof_Cent,Percentage,
                                                    Percentage_Update_Date,cost_elem,co_el_name,Vendor,VendorName,
                                                    Descr,UM,T_Curr,Sch_Lin,Ord_Qty,Open_Qty,Open_Amt_T,
                                                    Order_Amt,Open_Amt_L,Insert_Date,Update_Date)
                                                VALUES 
                                                    (@PONumber,@Item,@Update_Num,@latest,@Status,@Purchase_O,
                                                    @TSSC_Pending_Send_Date,@Requisitioner,@DeliveryDate,
                                                    @DeliveryDate_Req,@Cost_Cent,@Int_Order,@Prof_Cent,@Percentage,
                                                    @Percentage_Update_Date,@cost_elem,@co_el_name,@Vendor,@VendorName,
                                                    @Descr,@UM,@T_Curr,@Sch_Lin,@Ord_Qty,@Open_Qty,@Open_Amt_T,
                                                    @Order_Amt,@Open_Amt_L,@Insert_Date,@Update_Date)";

            SqlCommand SqlCmd2 = new SqlCommand(sClearSQL2, SqlConn2);

            SqlConn0.Open();
            SqlConn1.Open();
            SqlConn2.Open();

            SqlDataReader dr = SqlCmd0.ExecuteReader();

            int counter = 0;

            while (dr.Read())
            {
                counter++;
                
                //Update latest=0
                SqlCmd1.Parameters.Clear();
                SqlCmd1.Parameters.AddWithValue("Update_Date", System.DateTime.Now);
                SqlCmd1.Parameters.AddWithValue("PONumber", dr.GetInt64(dr.GetOrdinal("PONumber")));
                SqlCmd1.Parameters.AddWithValue("Item", dr.GetInt32(dr.GetOrdinal("Item")));
                SqlCmd1.ExecuteNonQuery();

                // Insert Closed with update_num++
                SqlCmd2.Parameters.Clear();
                SqlCmd2.Parameters.AddWithValue("PONumber", dr.GetInt64(dr.GetOrdinal("PONumber")));
                SqlCmd2.Parameters.AddWithValue("Item", dr.GetInt32(dr.GetOrdinal("Item")));
                SqlCmd2.Parameters.AddWithValue("Update_Num", dr.GetInt32(dr.GetOrdinal("Update_Num"))+1);  // +1 !!
                SqlCmd2.Parameters.AddWithValue("latest", 1);
                SqlCmd2.Parameters.AddWithValue("Status", 99);
                SqlCmd2.Parameters.AddWithValue("Purchase_O", dr.GetSqlDateTime(dr.GetOrdinal("Purchase_O")));
                SqlCmd2.Parameters.AddWithValue("TSSC_Pending_Send_Date", dr.GetSqlDateTime(dr.GetOrdinal("TSSC_Pending_Send_Date")));
                SqlCmd2.Parameters.AddWithValue("Requisitioner", dr.GetString(dr.GetOrdinal("Requisitio")));
                SqlCmd2.Parameters.AddWithValue("DeliveryDate", dr.GetSqlDateTime(dr.GetOrdinal("DeliveryDate")));
                SqlCmd2.Parameters.AddWithValue("DeliveryDate_Req", dr.GetSqlDateTime(dr.GetOrdinal("DeliveryDate_Req")));
                SqlCmd2.Parameters.AddWithValue("Cost_Cent", dr.GetString(dr.GetOrdinal("Cost_Cent")));
                SqlCmd2.Parameters.AddWithValue("Int_Order", dr.GetString(dr.GetOrdinal("Int_Order")));
                SqlCmd2.Parameters.AddWithValue("Prof_Cent", dr.GetString(dr.GetOrdinal("Prof_Cent")));
                SqlCmd2.Parameters.AddWithValue("Percentage", dr.GetInt32(dr.GetOrdinal("Percentage")));
                SqlCmd2.Parameters.AddWithValue("Percentage_Update_Date", dr.GetSqlDateTime(dr.GetOrdinal("Percentage_Update_Date")));
                SqlCmd2.Parameters.AddWithValue("cost_elem", dr.GetInt32(dr.GetOrdinal("cost_elem")));
                SqlCmd2.Parameters.AddWithValue("co_el_name", dr.GetString(dr.GetOrdinal("co_el_name")));
                SqlCmd2.Parameters.AddWithValue("Vendor", dr.GetInt32(dr.GetOrdinal("Vendor")));
                SqlCmd2.Parameters.AddWithValue("VendorName", dr.GetString(dr.GetOrdinal("VendorName")));
                SqlCmd2.Parameters.AddWithValue("Descr", dr.GetString(dr.GetOrdinal("Descr")));
                SqlCmd2.Parameters.AddWithValue("UM", dr.GetSqlString(dr.GetOrdinal("UM")));
                SqlCmd2.Parameters.AddWithValue("T_Curr", dr.GetString(dr.GetOrdinal("T_Curr")));
                SqlCmd2.Parameters.AddWithValue("Sch_Lin", dr.GetString(dr.GetOrdinal("Sch_Lin")));
                SqlCmd2.Parameters.AddWithValue("Ord_Qty", dr.GetFloat(dr.GetOrdinal("Ord_Qty")));
                SqlCmd2.Parameters.AddWithValue("Open_Qty", dr.GetFloat(dr.GetOrdinal("Open_Qty")));
                SqlCmd2.Parameters.AddWithValue("Open_Amt_T", dr.GetFloat(dr.GetOrdinal("Open_Amt_T")));
                SqlCmd2.Parameters.AddWithValue("Order_Amt", dr.GetFloat(dr.GetOrdinal("Order_Amt")));
                SqlCmd2.Parameters.AddWithValue("Open_Amt_L", dr.GetFloat(dr.GetOrdinal("Open_Amt_L")));
                SqlCmd2.Parameters.AddWithValue("Insert_Date", System.DateTime.Now);
                SqlCmd2.Parameters.AddWithValue("Update_Date", System.DateTime.Now);
                
                SqlCmd2.ExecuteNonQuery();
            }

            dr.Close();
            SqlConn0.Close();
            SqlConn1.Close();
            SqlConn2.Close();

            return counter;
        }

        static int HandleUpdatedPOs()
        {
            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            //PONumber, Item, update_num


            string sClearSQL0 = @"  Select 
                                    t1.Cost_Cent, t1.Int_Order, t1.Prof_Cent, t1.cost_elem, t1.co_el_name, t1.Vendor, t1.PONumber, 
                                    t1.Item, t1.Descr, t1.UM, t1.T_Curr, t1.VendorName, t1.Sch_Lin, t1.Requisitio, t1.Ord_Qty, 
                                    t1.Open_Qty, t1.Order_Amt, t1.Open_Amt_T, t1.Open_Amt_L, t1.DeliveryDate, t1.Purchase_O,
                                    t2.Update_Num, t2.TSSC_Pending_Send_Date, t2.DeliveryDate_Req, t2.Percentage, t2.Percentage_Update_Date
                                    from PO_Import t1
                                    INNER Join POes t2 ON t1.PONumber = t2.PONumber AND t1.Item = t2.Item 
                                    Where t2.latest=1 AND t2.status<>99 AND t1.Requisitio<>'' AND t1.Open_Amt_L <> 0
                                    AND (t1.Open_Amt_L <> t2.Open_Amt_L OR t1.Open_Amt_T <> t2.Open_Amt_T 
                                        OR t1.DeliveryDate <> t2.DeliveryDate OR t1.Open_Qty<>t2.Open_Qty)";
            
            SqlCommand SqlCmd0 = new SqlCommand(sClearSQL0, SqlConn);

            string sClearSQL1 = @"  UPDATE POes 
                                    SET latest=0, Update_Date=@Update_Date
                                    WHERE PONumber=@PONumber AND Item=@Item AND latest=1";

            SqlCommand SqlCmd1 = new SqlCommand(sClearSQL1, SqlConn);

            string sClearSQL2 = @"  INSERT INTO POes(PONumber,Item,Update_Num,latest,Status,Purchase_O,
                                                    TSSC_Pending_Send_Date,Requisitioner,DeliveryDate,
                                                    DeliveryDate_Req,Cost_Cent,Int_Order,Prof_Cent,Percentage,
                                                    Percentage_Update_Date,cost_elem,co_el_name,Vendor,VendorName,
                                                    Descr,UM,T_Curr,Sch_Lin,Ord_Qty,Open_Qty,Open_Amt_T,
                                                    Order_Amt,Open_Amt_L,Insert_Date,Update_Date)
                                                VALUES 
                                                    (@PONumber,@Item,@Update_Num,@latest,@Status,@Purchase_O,
                                                    @TSSC_Pending_Send_Date,@Requisitioner,@DeliveryDate,
                                                    @DeliveryDate_Req,@Cost_Cent,@Int_Order,@Prof_Cent,@Percentage,
                                                    @Percentage_Update_Date,@cost_elem,@co_el_name,@Vendor,@VendorName,
                                                    @Descr,@UM,@T_Curr,@Sch_Lin,@Ord_Qty,@Open_Qty,@Open_Amt_T,
                                                    @Order_Amt,@Open_Amt_L,@Insert_Date,@Update_Date)";

            SqlCommand SqlCmd2 = new SqlCommand(sClearSQL2, SqlConn);

            SqlConn.Open();

            SqlDataReader dr = SqlCmd0.ExecuteReader();

            int counter = 0;

            while (dr.Read())
            {
                counter++;

                //Update latest=0
                SqlCmd1.Parameters.Clear();
                SqlCmd1.Parameters.AddWithValue("Update_Date", System.DateTime.Now);
                SqlCmd1.Parameters.AddWithValue("PONumber", dr.GetInt64(dr.GetOrdinal("PONumber")));
                SqlCmd1.Parameters.AddWithValue("Item", dr.GetInt32(dr.GetOrdinal("Item")));
                SqlCmd1.ExecuteNonQuery();

                // Insert Closed with update_num++
                SqlCmd2.Parameters.Clear();
                SqlCmd2.Parameters.AddWithValue("PONumber", dr.GetInt64(dr.GetOrdinal("PONumber")));
                SqlCmd2.Parameters.AddWithValue("Item", dr.GetInt32(dr.GetOrdinal("Item")));
                SqlCmd2.Parameters.AddWithValue("Update_Num", dr.GetInt32(dr.GetOrdinal("Update_Num")) + 1);  // +1 !!
                SqlCmd2.Parameters.AddWithValue("latest", 1);
                SqlCmd2.Parameters.AddWithValue("Status", 0);
                SqlCmd2.Parameters.AddWithValue("Purchase_O", dr.GetSqlDateTime(dr.GetOrdinal("Purchase_O")));
                SqlCmd2.Parameters.AddWithValue("TSSC_Pending_Send_Date", dr.GetSqlDateTime(dr.GetOrdinal("TSSC_Pending_Send_Date")));
                SqlCmd2.Parameters.AddWithValue("Requisitioner", dr.GetString(dr.GetOrdinal("Requisitio")));
                SqlCmd2.Parameters.AddWithValue("DeliveryDate", dr.GetSqlDateTime(dr.GetOrdinal("DeliveryDate")));
                SqlCmd2.Parameters.AddWithValue("DeliveryDate_Req", dr.GetSqlDateTime(dr.GetOrdinal("DeliveryDate_Req")));
                SqlCmd2.Parameters.AddWithValue("Cost_Cent", dr.GetString(dr.GetOrdinal("Cost_Cent")));
                SqlCmd2.Parameters.AddWithValue("Int_Order", dr.GetString(dr.GetOrdinal("Int_Order")));
                SqlCmd2.Parameters.AddWithValue("Prof_Cent", dr.GetString(dr.GetOrdinal("Prof_Cent")));
                SqlCmd2.Parameters.AddWithValue("Percentage", dr.GetInt32(dr.GetOrdinal("Percentage")));
                SqlCmd2.Parameters.AddWithValue("Percentage_Update_Date", dr.GetSqlDateTime(dr.GetOrdinal("Percentage_Update_Date")));
                SqlCmd2.Parameters.AddWithValue("cost_elem", dr.GetInt32(dr.GetOrdinal("cost_elem")));
                SqlCmd2.Parameters.AddWithValue("co_el_name", dr.GetString(dr.GetOrdinal("co_el_name")));
                SqlCmd2.Parameters.AddWithValue("Vendor", dr.GetInt32(dr.GetOrdinal("Vendor")));
                SqlCmd2.Parameters.AddWithValue("VendorName", dr.GetString(dr.GetOrdinal("VendorName")));
                SqlCmd2.Parameters.AddWithValue("Descr", dr.GetString(dr.GetOrdinal("Descr")));
                SqlCmd2.Parameters.AddWithValue("UM", dr.GetSqlString(dr.GetOrdinal("UM")));
                SqlCmd2.Parameters.AddWithValue("T_Curr", dr.GetString(dr.GetOrdinal("T_Curr")));
                SqlCmd2.Parameters.AddWithValue("Sch_Lin", dr.GetString(dr.GetOrdinal("Sch_Lin")));
                SqlCmd2.Parameters.AddWithValue("Ord_Qty", dr.GetFloat(dr.GetOrdinal("Ord_Qty")));
                SqlCmd2.Parameters.AddWithValue("Open_Qty", dr.GetFloat(dr.GetOrdinal("Open_Qty")));
                SqlCmd2.Parameters.AddWithValue("Open_Amt_T", dr.GetFloat(dr.GetOrdinal("Open_Amt_T")));
                SqlCmd2.Parameters.AddWithValue("Order_Amt", dr.GetFloat(dr.GetOrdinal("Order_Amt")));
                SqlCmd2.Parameters.AddWithValue("Open_Amt_L", dr.GetFloat(dr.GetOrdinal("Open_Amt_L")));
                SqlCmd2.Parameters.AddWithValue("Insert_Date", System.DateTime.Now);
                SqlCmd2.Parameters.AddWithValue("Update_Date", System.DateTime.Now);

                SqlCmd2.ExecuteNonQuery();
            }

            dr.Close();
            SqlConn.Close();

            return counter;
        }

        /*
        static int HandleUpdatedPOs_old()
        {
            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            string sClearSQL = @"   SELECT PONumber, Item, DeliveryDate
                                    FROM PO_Import
                                    WHERE EXISTS (
                                    SELECT *
                                    FROM POes
                                    WHERE PONumber = PO_Import.PONumber
                                    AND Item = PO_Import.Item
                                    AND DeliveryDate <> PO_Import.DeliveryDate)";

            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);

            string sClearUpdateSQL = @" UPDATE POes 
                                        SET DeliveryDate = @DeliveryDate
                                        WHERE PONumber = @PONumber AND Item = @Item";

            SqlCommand SqlUpdateCmd = new SqlCommand(sClearUpdateSQL, SqlConn);


            SqlConn.Open();

            SqlDataReader dr = SqlCmd.ExecuteReader();

            int counter = 0;

            while (dr.Read())
            {
                counter++;
                SqlUpdateCmd.Parameters.Clear();
                SqlUpdateCmd.Parameters.AddWithValue("PONumber", dr.GetInt32(0));
                SqlUpdateCmd.Parameters.AddWithValue("Item", dr.GetInt32(1));
                SqlUpdateCmd.Parameters.AddWithValue("DeliveryDate", dr.GetDateTime(2));
                SqlUpdateCmd.ExecuteNonQuery();
            }

            dr.Close();
            SqlConn.Close();

            return counter;
        }
         */
    }
}
